package com.xiaominge.utils.excleUtils.excelMerge;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

/**
 * @Author xiaomin
 * @Description
 * @Date 2019/5/9 9:20
 */
public class ExcelMergeOutputUtils {

    public static Workbook getWorkbook(ExcelType excelType) {
        if (excelType == null) {
            return new XSSFWorkbook();
        }
        if (ExcelType.xls == excelType) {
            return new HSSFWorkbook();
        } else if (ExcelType.xlsx == excelType) {
            return new XSSFWorkbook();
        } else {
            return new XSSFWorkbook();
        }
    }

    public enum ExcelType {
        xlsx, xls
    }

    /**
     * 相应页面
     *
     * @param response
     * @param fileName
     * @param data
     * @throws Exception
     */
    public static void exportExcel(HttpServletResponse response, String fileName, MergeExcelData data) throws Exception {
        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称
        Workbook workbook;
        if (StringUtils.endsWithIgnoreCase(fileName, ".xlsx")) { //如果名称名义指定类型 手动拼接上
            workbook = getWorkbook(ExcelType.xlsx);
        } else if (StringUtils.endsWithIgnoreCase(fileName, ".xls")) {
            workbook = getWorkbook(ExcelType.xlsx);
        } else {
            fileName.concat(".xlsx");
            workbook = getWorkbook(ExcelType.xlsx);
        }
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
        //写入数据  并输出
        exportExcel(workbook, data, response.getOutputStream());
    }

    public static void exportExcel(String saveDir, String fileName, MergeExcelData data) throws IOException {
        // 告诉浏览器用什么软件可以打开此文件
        // 下载文件的默认名称
        Workbook workbook;
        if (StringUtils.endsWithIgnoreCase(fileName, ".xlsx")) { //如果名称名义指定类型 手动拼接上
            workbook = getWorkbook(ExcelType.xlsx);
        } else if (StringUtils.endsWithIgnoreCase(fileName, ".xls")) {
            workbook = getWorkbook(ExcelType.xlsx);
        } else {
            fileName = fileName.concat(".xlsx");
            workbook = getWorkbook(ExcelType.xlsx);
        }
        if (StringUtils.isBlank(saveDir)) {
            System.getProperty("user.dir").concat(File.separator).concat(fileName);
        } else {
            if (saveDir.equals(File.separator)) {
                saveDir = saveDir.concat(fileName);
            } else {
                saveDir = saveDir.concat(File.separator).concat(fileName);
            }
        }
        //写入数据  并输出
        exportExcel(workbook, data, new FileOutputStream(saveDir));
    }


    public static void exportExcel(Workbook wb, MergeExcelData data, OutputStream out) throws IOException {
        try {

            //写入数据
            writeExcel(wb, data);

            wb.write(out);
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //此处需要关闭 wb 变量
             wb.close();
            out.close();
        }
    }

    /**
     * 给指定页签写入数据
     *
     * @param wb   excle 对象
     * @param data 数据
     */
    public static void writeExcel(Workbook wb, MergeExcelData data) {

        //页签的名称
        String sheetName = data.getSheetName();
        if (null == sheetName) {
            sheetName = "Sheet1";
        }
        //创建一个一页 如果有就直获取 没有就创建
        Sheet sheet = wb.getSheet(sheetName);
        if (sheet == null) {
            sheet = wb.createSheet();
            //自己创建的sheet页 设置sheet名称
            wb.setSheetName(data.getSheetNum(), sheetName);
        }

        //设置标题  返回行号
        int rowIndex = writeTitlesToExcel(wb, sheet, data);
        //写入数据  返回行号
        writeRowsToExcel(wb, sheet, data, rowIndex);
        //设置合并单元格
        setMerge(sheet, data);
        //自适应宽度设置
        autoSizeColumns(sheet, data.getTitle().getExcelCellInfos().size() + 1);

        if (data.getRows() != null) {
            autoSizeColumns(sheet, data.getRows().size() + 1);
        }
       
    }

    /**
     * @param wb
     * @param sheet
     * @param excelData 全部数据 -->只写的标题;
     * @return
     */
    private static int writeTitlesToExcel(Workbook wb, Sheet sheet, MergeExcelData excelData) {
        int rowIndex = 0;


        //创标题样式  建样式对象
        Font titleFont = wb.createFont();
        titleFont.setFontName("Lucida Bright");
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 14);
        titleFont.setColor(IndexedColors.BLACK.index);

        // 创建单元格样式
        CellStyle titleStyle = wb.createCellStyle();

        titleStyle.setWrapText(true);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);

        setBorder(titleStyle, BorderStyle.THIN, IndexedColors.BLACK);
        ExcelRowInfo title = excelData.getTitle();
        if (title != null) {
            int colIndex = 0;
            if (title.getRowIndex() != null) { //如果没设置行  默认从第一行开始写
                rowIndex = title.getRowIndex();
            }
            //创建一行
            Row titleRow = sheet.createRow(rowIndex);
            titleRow.setHeightInPoints(25);      //设置行高

            for (ExcelCellInfo excelCollInfo : title.getExcelCellInfos()) {
                if (excelCollInfo.getCollIndex() != null) { //如果没事设置起止列 迷人从0开始时写
                    colIndex = excelCollInfo.getCollIndex();
                }
                Cell cell = titleRow.createCell(colIndex);
                cell.setCellValue(excelCollInfo.getContent()); //写入数据
                cell.setCellStyle(titleStyle); //设置样式

                //获取合并信息
                ExcelMergeData excelMergeData = excelCollInfo.getExcelMergeData();
                //判断该单元格是否有合并单元格信息
                if (excelMergeData != null) {
                    //判断合并信息是完整
                    if (excelMergeData.getStartCollIndex() == null) {
                        throw new RuntimeException("合并单元格信息中,没有设置开始的列");
                    }

                    if (excelMergeData.getEndCollIndex() == null) {
                        throw new RuntimeException("合并单元格信息中,没有设置结束列");
                    }
                    if (excelMergeData.getMergeRowNum() == null) {
                        throw new RuntimeException("合并单元格信息中,没有设置合并多少行");
                    }

                    //设置当前行  为起行
                    excelMergeData.setStartRowIndex(rowIndex);
                    //设置合并的终止行
                    excelMergeData.setEndRowIndex(rowIndex + excelMergeData.getMergeRowNum());

                    //将合并的信息放入集合.
                    excelData.getAllExcelMergeData().add(excelMergeData);
                }

                colIndex++;
            }
            rowIndex++;
        }
        return rowIndex;
    }

    /**
     * 数据写入
     *
     * @param wb        excle 对象
     * @param sheet     页签
     * @param excelData 数据对象 这里使用的是里面的数据
     * @param rowIndex  开始行号
     * @return
     */
    private static int writeRowsToExcel(Workbook wb, Sheet sheet, MergeExcelData excelData, int rowIndex) {


        Font dataFont = wb.createFont();
        dataFont.setFontName("Lucida Bright"); //设置字体
        dataFont.setFontHeightInPoints((short) 14); //设置字体大小
        dataFont.setColor(IndexedColors.BLACK.index);


        CellStyle dataStyle = wb.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.CENTER);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dataStyle.setFont(dataFont);
        setBorder(dataStyle, BorderStyle.THIN, IndexedColors.BLACK);
        //开启写入换行符
        dataStyle.setWrapText(true);

        List<ExcelRowInfo> rows = excelData.getRows();

        if (rows != null) {
            Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
            for (ExcelRowInfo oneRowdata : rows) {
                if (oneRowdata.getRowIndex() != null) { //如果没设置行  默认从第一行开始写
                    rowIndex = oneRowdata.getRowIndex();
                }
                //创建一行
                Row dataRow = sheet.createRow(rowIndex);
                int colIndex = 0;   //默认是从0开始写
                dataRow.setHeightInPoints(20);      //设置行高

                for (ExcelCellInfo excelCellInfo : oneRowdata.getExcelCellInfos()) {
                    if (excelCellInfo.getCollIndex() != null) { //如果没有设置起止列 默认从0开始时写
                        colIndex = excelCellInfo.getCollIndex();
                    }
                    Cell cell = dataRow.createCell(colIndex); //创建单元格


                    String cellData = excelCellInfo.getContent();
                    if (cellData != null) {

                        cell.setCellValue(cellData);
                        //  cell.setCellValue( new org.apache.poi.xssf.usermodel.XSSFRichTextString(cellData));
                    } else if (excelCellInfo.getImageBytes()!=null){
                        //写入图片
                        dataRow.setHeightInPoints(100);      //设置行高
                        sheet.autoSizeColumn(rowIndex, true);
                        sheet.setColumnWidth(colIndex, 20*256); //列宽 一个字符宽度 256
                        /**
                         * 该构造函数有8个参数
                         * 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
                         * 后四个参数，前连个表示图片左上角所在的cellNum和 rowNum，后天个参数对应的表示图片右下角所在的cellNum和 rowNum，
                         * excel中的cellNum和rowNum的index都是从0开始的
                         *
                         */
                        //图片一导出到单元格B2中
                        ClientAnchor anchor=drawingPatriarch.createAnchor(0, 0, 0, 0,
                                (short) colIndex, rowIndex, (short)( colIndex+1), rowIndex+1);

                        drawingPatriarch.createPicture(anchor,wb.addPicture(excelCellInfo.getImageBytes(),excelCellInfo.getImageType()));

                    }else {
                        cell.setCellValue("");
                    }
                    cell.setCellStyle(dataStyle); //设置单元的样式

                    //获取合并信息
                    ExcelMergeData excelMergeData = excelCellInfo.getExcelMergeData();

                    //判断该单元格是否有合并单元格信息
                    if (excelMergeData != null) {
                        //判断合并信息是完整
                        if (excelMergeData.getStartCollIndex() == null) {
                            throw new RuntimeException("合并单元格信息中,没有设置开始的列");
                        }

                        if (excelMergeData.getEndCollIndex() == null) {
                            throw new RuntimeException("合并单元格信息中,没有设置结束列");
                        }
                        if (excelMergeData.getMergeRowNum() == null) {
                            throw new RuntimeException("合并单元格信息中,没有设置合并多少行");
                        }

                        //设置当前行  为起行
                        excelMergeData.setStartRowIndex(rowIndex);
                        //设置合并的终止行
                        excelMergeData.setEndRowIndex(rowIndex + excelMergeData.getMergeRowNum());

                        //将合并的信息放入集合.
                        excelData.getAllExcelMergeData().add(excelMergeData);
                    }
                    colIndex++;
                }
                rowIndex++;
            }
        }

        return rowIndex;
    }


    /**
     * 设置宽度
     *
     * @param sheet
     * @param columnNumber
     */
    private static void autoSizeColumns(Sheet sheet, int columnNumber) {

        for (int i = 0; i < columnNumber; i++) {
            int orgWidth = sheet.getColumnWidth(i);
            // 调整每一列宽度
            sheet.autoSizeColumn(i, true);
            // 解决自动设置列宽中文失效的问题
            int newWidth = (int) (orgWidth + 100);  //+100---->* 17 / 10
            int maxWith = 256*255;
            //限制下最大宽度
            if(newWidth > maxWith) {
                sheet.setColumnWidth(i, maxWith);
            }else if (newWidth > orgWidth) {
                sheet.setColumnWidth(i, newWidth);
            } else {
                sheet.setColumnWidth(i, orgWidth);
            }
        }
    }

    /**
     * 设置样式
     *
     * @param style
     * @param border
     * @param color
     */
    private static void setBorder(CellStyle style, BorderStyle border, IndexedColors color) {

        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBottomBorderColor(color.getIndex());
        style.setTopBorderColor(color.getIndex());
        style.setLeftBorderColor(color.getIndex());
        style.setRightBorderColor(color.getIndex());
    }


    /**
     * 设置合并的单元格
     * 参数：起始行号，终止行号， 起始列号，终止列号
     */
    public static void setMerge(Sheet sheet, MergeExcelData exceldata) {
        exceldata.getAllExcelMergeData().forEach(excelMergeData -> {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(
                    excelMergeData.getStartRowIndex(),
                    excelMergeData.getEndRowIndex(),
                    excelMergeData.getStartCollIndex(),
                    excelMergeData.getEndCollIndex());

            sheet.addMergedRegion(cellRangeAddress);

            // 使用RegionUtil类为合并后的单元格添加边框
            RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet); // 下边框
            RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet); // 左边框
            RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet); // 右边框
            RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet); // 上边框
        });

    }
}
